package track;

import io.SqlDb;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * this class holds all the function that manage tracks
 * 
 * @author Alex
 *
 */
public class TracksFuncs {

	/**
	 * Returns the genre by it's name (a factory)
	 * 
	 * @param genreName - the name of the searched genre
	 * 
	 * @post if the genre didn't exist, then it will be created in the database and returned
	 * 
	 * @return the genre buy it's name
	 */
	public static Genre getGenreFactory(String genreName)
	{
		Statement stmt;
		ResultSet result;
		genreName = genreName.replace("'", "");
		Genre genre = null;
		
		genreName = genreName.substring(0 , 1).toUpperCase() + genreName.substring(1); 
		
		try {
			stmt = SqlDb.getConnection().createStatement();
			result = stmt.executeQuery("SELECT id " +
									   "FROM genres " +
									   "WHERE name='" + genreName + "'");
			
			result.next();
			
			if(result.getRow() != 1) {				
				SqlDb.executeQuery("INSERT INTO genres(name) VALUES('" + genreName + "')");
				
				result = stmt.executeQuery("SELECT GENRES_SEQ.CURRVAL FROM DUAL");
				result.next();

				int genreId = result.getInt(1);
				
				genre = new Genre(genreId);;
				stmt.close();
				result.close();
				return genre;
			}
			else {
				genre = new Genre(result.getInt(1));
				stmt.close();
				result.close();
				return genre;
			}
		} 
		catch (SQLException e) {
			return null;
		}
	}
	
	/**
	 * Returns the sub genre by it's name (a factory)
	 * 
	 * @param subGenreName - the name of the searched genre
	 * @param Genre genre the genre that the sub genre belongs to
	 * 
	 * @post if the genre didn't exist, then it will be created in the database and returned
	 * 
	 * @return the genre buy it's name
	 */
	public static SubGenre getSubGenreFactory(String subGenreName, Genre genre)
	{
		Statement stmt;
		ResultSet result;
		SubGenre subGenre = null;
		
		subGenreName = subGenreName.replace("'", "");
		subGenreName = subGenreName.substring(0 , 1).toUpperCase() + subGenreName.substring(1); 
		
		try {
			stmt = SqlDb.getConnection().createStatement();
			result = stmt.executeQuery("SELECT id " +
									   "FROM sub_genres " +
									   "WHERE name='" + subGenreName + "'");

			result.next();
			
			if(result.getRow() != 1) {
				SqlDb.executeQuery("INSERT INTO sub_genres(name,genre_id) VALUES('" + subGenreName + "','" + genre.getId() + "')");
				
				result = stmt.executeQuery("SELECT SUB_GENRES_SEQ.CURRVAL FROM DUAL");
				result.next();

				int subGenreId = result.getInt(1);

				subGenre = new SubGenre(subGenreId);
				stmt.close();
				result.close();
				return subGenre;
			}
			else {
				subGenre = new SubGenre(result.getInt(1));
				stmt.close();
				result.close();
				return subGenre;
			}
		} 
		catch (SQLException e) {
			return null;
		}
	}
	
	/**
	 * Returns the artist by it's name
	 * 
	 * @param artistname
	 * 
	 * @post if the artist doesn't exist, a new one is created 

	 * @return the artist
	 */
	public static Artist getArtistFactory(String artistName)
	{
		Statement stmt;
		ResultSet result;
		Artist artist;
		
		artistName = artistName.replace("'", "");
		
		if(artistName.equals(""))
			artistName = "Various";
		
		try {
			stmt = SqlDb.getConnection().createStatement();
			result = stmt.executeQuery("SELECT id " +
									   "FROM artists " +
									   "WHERE name='" + artistName + "'");			
			
			result.next();
			
			if(result.getRow() != 1) {
				SqlDb.executeQuery("INSERT INTO artists(name) VALUES('" + artistName + "')");
			
				result = stmt.executeQuery("SELECT ARTISTS_SEQ.CURRVAL FROM DUAL");
				result.next();

				int artistId = result.getInt(1);
				artist = new Artist(artistId);
				result.close();
				return artist;
			}
			else {
				artist = new Artist(result.getInt(1));
				stmt.close();
				result.close();
				return artist;
			}
		} 
		catch (SQLException e) {
			return null;
		}
	}
	
	/**
	 * Returns the track by it's parameters
	 * 
	 * @param trackName
	 * @param artist
	 * @param disc
	 * @param orderNum
	 * 
	 * @return the track
	 */
	public static Track createTrackFactory(String trackName, Artist artist, Disc disc, int orderNum)
	{		
		Statement stmt;
		ResultSet result;
		
		Track track = null;
		
		trackName = trackName.replace("'", "");
		
		if(trackName.equals(""))
			trackName = "Various";
		
		try {
			stmt = SqlDb.getConnection().createStatement();
			result = stmt.executeQuery("SELECT id " +
									   "FROM tracks " +
									   "WHERE name='" + trackName + "' AND " +
									   		"artist_id='" + artist.getId() + "'");			
			
			result.next();
			
			if(result.getRow() != 1) {
				SqlDb.executeQuery("INSERT INTO tracks(name,artist_id,disc_id,order_num) " +
						"VALUES('" + trackName + "','" + artist.getId() + "','" + 
						disc.getId() + "','" + orderNum + "')");
	
				stmt = SqlDb.getConnection().createStatement();
				result = stmt.executeQuery("SELECT TRACKS_SEQ.CURRVAL FROM DUAL");
				result.next();
			
				int trackId = result.getInt(1);
				
				track = new Track(trackId);
				stmt.close();
				result.close();
				return track;
			}
			else {
				track = new Track(result.getInt(1));
				stmt.close();
				result.close();
				return track;
			}
		} 
		catch (SQLException e) {
			return null;
		}
	}

	/**
	 * Return a disc by it's parameters
	 * 
	 * @param discNum
	 * @param discName
	 * @param subGenre
	 * 
	 * @return the disc
	 */
	public static Disc createDiscFactory(int discNum, String discName, SubGenre subGenre) 
	{
		Statement stmt;
		ResultSet result;
		Disc disc = null;
		
		discName = discName.replace("'", "");
		
		try {
			stmt = SqlDb.getConnection().createStatement();
			result = stmt.executeQuery("SELECT COUNT(*) " +
									   "FROM discs " +
									   "WHERE id='" + discNum + "'");			
			
			result.next();
			
			if(result.getInt(1) == 0) {
				SqlDb.executeQuery("INSERT INTO discs(id,name,sub_genre_id) " +
						"VALUES('" + discNum + "','" + discName + "','" + subGenre.getId() + "')");
			}

			disc = new Disc(discNum);
			stmt.close();
			result.close();
			return disc;
		} 
		catch (SQLException e) {
			return null;
		}

	}
}
